/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import models.Customer;
import models.Invoice;
import models.Order;

/**
 *
 * @author pelle
 */
public class InvoiceDb extends AbstractDb implements InvoiceInterface {

    public Invoice getInvoice(int invoiceId) {
        Invoice invoice = null;
        OrderInterface orderDb = new OrderDb();

        if(invoiceId > 0) {
            try {
                ResultSet rs = this.db.query(""
                        + "SELECT * "
                        + "FROM invoice "
                        + "WHERE invoice_id = " + invoiceId);

                if(rs != null && rs.next()) {
                    Invoice inv = new Invoice(
                            rs.getInt("invoice_id"),
                            rs.getString("invoice_paymentDate"),
                            rs.getInt("invoice_amount"),
                            rs.getInt("invoice_status"),
                            rs.getFloat("invoice_discount"),
                            rs.getFloat("invoice_totalPrice"));

                    ResultSet rs_orders = this.db.query(""
                            + "SELECT invoiceOrders_orderid "
                            + "FROM InvoiceOrders "
                            + "WHERE invoiceOrders_invoiceid = " + invoiceId);

                    if(rs_orders != null) {
                        Customer customer = null;
                        while(rs_orders.next()) {

                            Order od = orderDb.getOrder(rs_orders.getInt("invoiceOrders_orderid"));

                            if(customer == null) {
                                customer = od.getCustomer();
                            }

                            inv.addOrder(od);
                        }

                        inv.setCustomer(customer);
                        invoice = inv;
                    }
                }
            } catch (SQLException ex) {
                Logger.getLogger(EmployeeDB.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return invoice;
    }

    public int createInvoice(Invoice invoice) {
        int invoiceId = -1;

        if(invoice != null) {
            ArrayList<Order> orders = invoice.getOrders();

            if(orders.size() > 0) {

                int res = this.db.queryInsert(""
                        + "INSERT INTO Invoice "
                        + "(invoice_paymentDate, invoice_amount, invoice_status, invoice_discount, invoice_totalPrice) "
                        + "VALUES ("
                        // 14 days of delivery
                        + "DATEADD(day, 14, GETDATE()), "
                        + invoice.getAmount() + ", "
                        + "0, "
                        + invoice.getDiscount() + ", "
                        + invoice.getTotalPrice() + ""
                        + ");");
                
                if(res > 0) {
                    int invId = this.db.getInsertId();

                        String query = ""
                            + "INSERT INTO InvoiceOrders "
                            + "(invoiceOrders_invoiceid, invoiceOrders_orderid) "
                            + "VALUES ";

                        String queryValues = "";
                            for(Order o : orders) {
                                if(queryValues.length() > 0) {
                                    queryValues += ", ";
                                }

                                queryValues += " (" + invId + ", " + o.getId() + ")";
                            }

                    int res2 = this.db.queryInsert(query + queryValues);

                    if(res2 > 0) {
                        invoiceId = invId;
                    }
                }
            }
        }

        return invoiceId;
    }

}
